﻿using STHPMS1.DAL.D_Core;
using STHPMS1.ICore.I_DAL.Repository;
using STHPMS1.ICore.I_Entity.Stmpms1;
using System.Collections.Generic;
using System.Data;
using ServiceStack.OrmLite;
using System.Text;
using System.Data.SqlClient;
using System;
using System.Linq.Expressions;
using ServiceStack.OrmLite.SqlServer;

namespace STHPMS1.DAL.D_Repository
{
    public class B_RdstandardRepository : DalBase, IB_RdstandardRepository
    {

        public void CheckMp(string rdid,double begin,double end)
        {
            string sql = string.Format("select BEGINMP from B_RDSTANDARD where CHANGETYPE_DICTID=207 and RDWAYID='{0}' and BEGINMP={1} and value={2}", rdid, begin,end);
            using (IDbConnection conn = Adapter.OpenDbConnection())
            {

                    IDbCommand cmmd = conn.CreateCommand();
                    cmmd.CommandText = sql.ToString();
                    object obj = cmmd.ExecuteScalar();
                    if (obj == null)
                        throw new Exception("交通量桩号" + begin + "不符合路线" + rdid + "的交通量划分标准");
            }
        }
        public DataTable GetRdStandard()
        {
            string sql = @"
select 
c.Rdwayid ,
c.Name,
c.Direction_Dictid,
SUM(CASE CHANGETYPE_DICTID WHEN 202 THEN stake END) as [R202],
SUM(CASE CHANGETYPE_DICTID WHEN 203 THEN stake END) as [R203],
SUM(CASE CHANGETYPE_DICTID WHEN 204 THEN stake END) as [R204],
SUM(CASE CHANGETYPE_DICTID WHEN 205 THEN stake END) as [R205],
SUM(CASE CHANGETYPE_DICTID WHEN 206 THEN stake END) as [R206],
SUM(CASE CHANGETYPE_DICTID WHEN 207 THEN stake END) as [R207],
SUM(CASE CHANGETYPE_DICTID WHEN 208 THEN stake END) as [R208],
SUM(CASE CHANGETYPE_DICTID WHEN 209 THEN stake END) as [R209],
SUM(CASE CHANGETYPE_DICTID WHEN 210 THEN stake END) as [R210],
SUM(CASE CHANGETYPE_DICTID WHEN 211 THEN stake END) as [R211]
FROM
(select a.RDWAYID,a.Name,b.DIRECTION_DICTID,b.CHANGETYPE_DICTID,b.stake 
	from B_ROADWAY a 
	left JOIN
		(SELECT RDWAYID,DIRECTION_DICTID,CHANGETYPE_DICTID,count(BEGINMP) as stake  
			from B_RDSTANDARD 
			GROUP BY RDWAYID,DIRECTION_DICTID, CHANGETYPE_DICTID) b
	on a.RDWAYID=b.RDWAYID) c
GROUP BY RDWAYID,Name,DIRECTION_DICTID";
            return Adapter.DataAdapter.FillDataTable(sql);
        }
    }
}
